package benny_driving_ser.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import benny_driving_ser.constant.ParamConstant;
import benny_driving_ser.dao.LocationShareDao;
import benny_driving_ser.entity.DriLocation;
import benny_driving_ser.util.DBUtil;
import benny_driving_ser.util.FormatUtil;

public class LocationShareDaoImpl implements LocationShareDao {
	private LocationShareDaoImpl() {

	}

	private static class LocationShareDaoImplHolder {
		private static LocationShareDaoImpl instance = new LocationShareDaoImpl();
	}

	public static LocationShareDaoImpl getInstance() {
		return LocationShareDaoImplHolder.instance;
	}

	public int shareLocation(String id, String log, String lat) {
		String time = System.currentTimeMillis() + "";
		int driid = Integer.parseInt(id.trim());
		String sql_add = "INSERT INTO t_sj_xy (sjuserid,x,y,addtime) VALUES (?,?,?,?)";
		String sql_update = "UPDATE t_sj_xy SET x = ? ,y = ? ,addtime = ? WHERE sjuserid = ?";
		String sql_query = "SELECT sjuserid FROM t_sj_xy WHERE sjuserid="
				+ driid;
		Connection con = DBUtil.getInstance().openConnection();
		PreparedStatement ps;
		try {
			con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			con.setAutoCommit(false);
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(sql_query);
			if (rs.next()) {
				ps = con.prepareStatement(sql_update);
				ps.setString(1, log);
				ps.setString(2, lat);
				ps.setString(3, time);
				ps.setInt(4, driid);
				ps.executeUpdate();
				con.commit();
				System.out.println("update");
				DBUtil.getInstance().closeConnection(con);
				return 0;
			}
			ps = con.prepareStatement(sql_add);
			ps.setInt(1, driid);
			ps.setString(2, log);
			ps.setString(3, lat);
			ps.setString(4, time);
			System.out.println("add");
			ps.executeUpdate();
			con.commit();
			DBUtil.getInstance().closeConnection(con);
			return 0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.getInstance().closeConnection(con);
		return 1;
	}

	public List<DriLocation> getDriInRange(String log, String lat) {
		double d_log = Double.parseDouble(log);
		double d_lat = Double.parseDouble(lat);
		List<DriLocation> driLocs = new ArrayList<DriLocation>();
		String sql_get_pos = "SELECT sjuserid,x,y,addtime FROM t_sj_xy";
		String sql_get_stars = "SELECT stars FROM t_user WHERE id=";
		String sql_get_state = "SELECT state FROM t_user WHERE id=";
		Connection con = DBUtil.getInstance().openConnection();
		try {
			con.setAutoCommit(false);
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(sql_get_pos);
			while (rs.next()) {
				if ("null".equals(rs.getString(2))
						|| "null".equals(rs.getString(3)))
					continue;
				int driid = rs.getInt(1);
				double getLog = Double.parseDouble(rs.getString(2));
				double getLat = Double.parseDouble(rs.getString(3));
				String time = rs.getString(4);
				double dis = FormatUtil.gps2m(d_lat, d_log, getLat, getLog);
				if (dis < ParamConstant.DRI_LOC_DIS) {
					Statement mS = con.createStatement();
					ResultSet mRs = mS.executeQuery(sql_get_stars + driid);
					int stars = -1;
					int state = -1;
					if (mRs.next())
						stars = mRs.getInt(1);
					mRs = mS.executeQuery(sql_get_state + driid);
					if (mRs.next())
						state = mRs.getInt(1);
					driLocs.add(new DriLocation(driid, getLog, getLat, time,
							dis, state, stars));
				}
			}
			con.commit();
			return driLocs;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.getInstance().closeConnection(con);
		}
		return null;
	}

	public int clearLocData(int id) {
		String sql_delete = "DELETE FROM t_sj_xy WHERE sjuserid=" + id;
		Connection con = DBUtil.getInstance().openConnection();
		try {
			Statement s = con.createStatement();
			s.executeUpdate(sql_delete);
			DBUtil.getInstance().closeConnection(con);
			return 0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.getInstance().closeConnection(con);
		return 1;
	}
}
